/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Connection;

import edu.fpt.common.HibernateUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

/**
 *
 * @author Trung
 */
public class savingConnection {

    public static void insert(String name, String type, Double amount, Date create, Date pre) throws ClassNotFoundException, SQLException {
        Connection con = null;
        PreparedStatement pstmt = null;

        String sql = "INSERT INTO Saving(Name, Type, Amount,CreatedTime,PreTime)"
                + "VALUES (?,?,?,?,?)";

        try {
            con = ConnectionUtitility.openConnection();
            pstmt = con.prepareStatement(sql);

            pstmt.setString(1, name);
            pstmt.setString(2, type);
            pstmt.setString(3, amount.toString());
            pstmt.setString(4, create.toString());
            pstmt.setString(5, pre.toString());

            pstmt.executeUpdate();
        } finally {
            try {
                pstmt.close();
                con.close();
            } catch (Exception e) {
            }
        }


    }

    public static void insertSaving(String name, String type, double amount, int month, int year) {
        Session session = null;
        Transaction tran = null;
        try {
            session = (Session) HibernateUtil.getSessionFactory().openSession();
            tran = session.beginTransaction();
            Query query = session.createSQLQuery("INSERT INTO Saving(Name, Type, Amount, Month, Year)"
                    + " VALUES('" + name + "','" + type + "'," + amount + "," + month + "," + year + ")");
            query.executeUpdate();
            tran.commit();
        } finally {
            session.close();
        }
    }

    public static List LishAll() {
        Session session = null;
        Transaction tran = null;
        try {
            session = (Session) HibernateUtil.getSessionFactory().openSession();
            tran = session.beginTransaction();

            //   Query query = session.createQuery("from Saving where Type = :code");
            Query query = session.createQuery("from Saving");
            //   query.setParameter("code", flag);
            List list = query.list();

            tran.commit();
            return list;
        } finally {
            session.close();
        }
    }

    public static void deleteSaving(int id) {
        Session session = null;
        Transaction tran = null;
        try {
            session = (Session) HibernateUtil.getSessionFactory().openSession();
            tran = session.beginTransaction();
            Query query = session.createSQLQuery("DELETE FROM Saving"
                    + " Where id=" + id +  "");
            query.executeUpdate();
            tran.commit();
        } finally {
            session.close();
        }


    }

    public static void editSaving(int id, String name, String type, double amount, int month, int year) {
        Session session = null;
        Transaction tran = null;
        try {
            session = (Session) HibernateUtil.getSessionFactory().openSession();
            tran = session.beginTransaction();
            String sql;
            sql = "UPDATE Saving "
                    + "SET name='"+name+"',"
                    + " type='"+type+"',amount='"+amount+"', month='"+month+"', year='"+year+"'"
                    + " WHERE id='"+id+"'";
            
            
            Query query = session.createSQLQuery(sql);
            
            query.executeUpdate();
            tran.commit();
        } finally {
            session.close();
        }

    }

    public static float getSumAmount(int month, int year) throws SQLException, ClassNotFoundException {
        Connection con = null;
        ResultSet rs = null;
        float sum;
        PreparedStatement psmt = null;
        //String sql = "select top(1) * from Orders order by OrderID DESC";
        String sql = "select sum(Amount)[sum] from Budget where Month(CreatedTime)='" + month + "' and year(CreatedTime)='" + year + "'";
        try {
            con = ConnectionUtitility.openConnection();
            psmt = con.prepareStatement(sql);
            rs = psmt.executeQuery();
            rs.next();
            //orderId = rs.getInt("OrderID");
            sum = rs.getFloat("sum");
        } finally {
            try {
                psmt.close();
                con.close();
            } catch (Exception e) {
            }
        }
        return sum;

    }

}